﻿using Dapper;
using System;
using System.Linq;
using System.Text;
using System.Data;
using Ayatta.Contract;
using System.Xml.Linq;
using Ayatta.Service.Extension;
using Ayatta.Contract.Domain;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class TradeService : BaseService, ITradeService
    {
        #region Order

        public MagicKit OrderCreate(Order o)
        {
            var result = new MagicKit();
            var template = new SqlBuilder()
                .Insert(Database.Trade.Table.Order)
                .Values(o.Id, o.Category, o.Quantity, o.SubTotal, o.Postage, o.Discount, o.Total, o.Paid, o.IntegralUse,
                        o.IntegralRealUse, o.IntegralReward, o.Coupon, o.CouponUse, o.GiftCard, o.GiftCardUse, o.Weight,
                        o.ETicket, o.Payment, o.Shipment, o.MultipleConsign, o.PaidOn, o.ConsignOn, o.FinishedOn,
                        o.ExpirationOn, o.Receiver, o.Phone, o.Mobile, o.AreaId, o.Province, o.City, o.District,
                        o.Address, o.PostalCode, o.HasInvoice, o.InvoiceCategory, o.InvoiceTitle, o.InvoiceContent,
                        o.BuyerId, o.BuyerName, o.BuyerFlag, o.BuyerMemo, o.BuyerMessage, o.BuyerRate, o.SellerId,
                        o.SellerName, o.SellerFlag, o.SellerMemo, o.SellerRate, o.Cancel, o.CancelReason, o.Exchange,
                        o.AssociateId, o.Extra, o.Status, o.IpAddress, o.TraceCode, o.CreatedBy, o.CreatedOn,
                        o.ModifiedOn)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    connection.Execute(template.Sql, template.Param, transaction);

                    foreach (var item in o.OrderItems)
                    {
                        template = new SqlBuilder()
                            .Insert(Database.Trade.Table.OrderItem)
                            .Values(item.Id, item.OrderId, item.SkuId, item.ItemId, item.ProductId, item.CategoryId,
                                    item.CategoryRootId, item.OuterId, item.Name, item.Quantity, item.Price,
                                    item.Discount, item.Adjust, item.Total, item.MealId, item.MealName,
                                    item.Picture, item.PropText, item.Shipment, item.BuyerId, item.SellerId,
                                    item.AuctionId, item.AuctionGrade, item.IsGift, item.IsService, item.IsVirtual,
                                    item.ConsignOn, item.FinishedOn, item.ExpirationOn, item.BindId,
                                    item.LogisticsCompany, item.ExpressNo, item.RefundId, item.RefundStatus,
                                    item.Extra, item.Status, item.CreatedBy, item.CreatedOn, item.ModifiedOn)
                            .Complete();

                        connection.Execute(template.Sql, template.Param, transaction);
                    }
                    transaction.Commit();
                    result.Status = true;
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result.Message = e.Message;
                }
            }
            return result;
        }

        public bool CreateGameOrder(Order o)
        {
            var builder = new RegexSqlBuilder()
            .Table(Database.Order.Table.Order)
            .Field("([Id],[Category],[Quantity],[Postage],[SubTotal],[Discount],[Total],[Paid],[UseIntegral],[RealUseIntegral],[DonateIntegral],[Weight],[PaymentType],[ShippingType],[PayTime],[ConsignTime],[EndTime],[ExpirationTime],[ETicketExtra],[MultipleConsign],[Receiver],[Phone],[Mobile],[AreaId],[Province],[City],[District],[Address],[PostalCode],[HasInvoice],[InvoiceCategory],[InvoiceTitle],[InvoiceContent],[IpAddress],[AssociatedId],[TraceCode],[BuyerId],[BuyerNick],[BuyerFlag],[BuyerMemo],[BuyerMessage],[BuyerRate],[SellerId],[SellerNick],[SellerFlag],[SellerMemo],[SellerRate],[Cancel],[CancelReason],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@Id,@Category,@Quantity,@Postage,@SubTotal,@Discount,@Total,@Paid,@UseIntegral,@RealUseIntegral,@DonateIntegral,@Weight,@PaymentType,@ShippingType,@PayTime,@ConsignTime,@EndTime,@ExpirationTime,@ETicketExtra,@MultipleConsign,@Receiver,@Phone,@Mobile,@AreaId,@Province,@City,@District,@Address,@PostalCode,@HasInvoice,@InvoiceCategory,@InvoiceTitle,@InvoiceContent,@IpAddress,@AssociatedId,@TraceCode,@BuyerId,@BuyerNick,@BuyerFlag,@BuyerMemo,@BuyerMessage,@BuyerRate,@SellerId,@SellerNick,@SellerFlag,@SellerMemo,@SellerRate,@Cancel,@CancelReason,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Id, o.Category, o.Quantity, o.Postage, o.SubTotal, o.Discount, o.Total, o.Paid, UseIntegral = o.IntegralUse, RealUseIntegral = o.IntegralRealUse, DonateIntegral = o.IntegralReward, o.Weight, PaymentType = o.Payment, ShippingType = o.Shipment, PayTime = o.PaidOn, ConsignTime = o.ConsignOn, EndTime = o.FinishedOn, ExpirationTime = o.ExpirationOn, ETicketExtra = o.ETicket, o.MultipleConsign, o.Receiver, o.Phone, o.Mobile, o.AreaId, o.Province, o.City, o.District, o.Address, o.PostalCode, o.HasInvoice, o.InvoiceCategory, o.InvoiceTitle, o.InvoiceContent, o.IpAddress, AssociatedId = o.AssociateId, o.TraceCode, o.BuyerId, BuyerNick = o.BuyerName, o.BuyerFlag, o.BuyerMemo, o.BuyerMessage, o.BuyerRate, o.SellerId, SellerNick = o.SellerName, o.SellerFlag, o.SellerMemo, o.SellerRate, o.Cancel, o.CancelReason, o.Extra, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });
            var template = builder.Complete(RegexSqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        /*
        public bool CreateOrder(Order o)
        {
            bool result;
            const string field = @"([Id],[UserId],[Category],[Quantity],[SubTotal],[ShippingCharge],[Discount],[Total],[Paid],[Refunded],[Weight],[ShippingMethod],[PaymentMethod],[PaidDate],[ShippingDate],[DeliveryDate],[OrderStatus],[ShippingStatus],[PaymentStatus],[AssociatedId],[Expiration],[AreaCode],[IpAddress],[TraceCode],[CreatedBy],[CreatedOn])
                                VALUES(@Id,@UserId,@Category,@Quantity,@SubTotal,@ShippingCharge,@Discount,@Total,@Paid,@Refunded,@Weight,@ShippingMethod,@PaymentMethod,@PaidDate,@ShippingDate,@DeliveryDate,@OrderStatus,@ShippingStatus,@PaymentStatus,@AssociatedId,@Expiration,@AreaCode,@IpAddress,@TraceCode,@CreatedBy,@CreatedOn)";
            var builder = new RegexSqlBuilder()
                .Table(Database.Order.Table.Order).Field(field)
                .AddParameters(new { o.Id, o.UserId, o.Category, o.Quantity, o.SubTotal, o.ShippingCharge, o.Discount, o.Total, o.Paid, o.Refunded, o.Weight, o.ShippingMethod, o.PaymentMethod, o.PaidDate, o.ShippingDate, o.DeliveryDate, o.OrderStatus, o.ShippingStatus, o.PaymentStatus, o.AssociatedId, o.Expiration, o.AreaCode, o.IpAddress, o.TraceCode, o.CreatedBy, o.CreatedOn });

            var template = builder.Complete(RegexSqlTemplate.Insert);

            var builderA = new RegexSqlBuilder()
                .Table(Database.Order.Table.OrderItem)
                .Field("([OrderId],[UserId],[ProductId],[ProductName],[ProductVariantId],[ProductVariantName],[Quantity],[Discount],[UnitPrice],[ProductAttribute],[ProductAttributeXml],[CreatedBy],[CreatedOn])VALUES(@UserId,@OrderId,@ProductId,@ProductName,@ProductVariantId,@ProductVariantName,@Quantity,@Discount,@UnitPrice,@ProductAttribute,@ProductAttributeXml,@CreatedBy,@CreatedOn)");
            
            var templateA = builderA.Complete(RegexSqlTemplate.Insert);

            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    result = Execute(template.Sql,template.Param, transaction) == 1;
                    if (result)
                    {
                        ProcessOrder(o);
                        result = BatchInsert(template.Sql,template.ParamA.RawSql, o.OrderItems, transaction) > 0;
                    }
                    if (!result)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result = false;
                }
            }
            return result;

        }
        */

        public bool OrderAddressInvoiceSet(string id, int userId, UserAddress address, UserInvoice invoice)
        {
            var builder = new RegexSqlBuilder()
                .Table(Database.Order.Table.Order)
                .Field("[Receiver]=@Name", new { address.Name })
                .Field("[Phone]=@Phone", new { address.Phone })
                .Field("[Mobile]=@Mobile", new { address.Mobile })
                .Field("[AreaId]=@AreaId", new { address.AreaId })
                .Field("[Province]=@Province", new { address.Province })
                .Field("[City]=@City", new { address.City })
                .Field("[District]=@District", new { address.District })
                .Field("[Address]=@Address", new { address.Address })
                .Field("[PostalCode]=@PostalCode", new { address.PostalCode })
                .Field("[Sataus]=@Sataus", new { OrderStatus.WaitBuyerPay })
                .Where("[Id]=@Id AND [BuyerId]=@UserId", new { id, userId });

            if (invoice == null)
            {
                builder.Field("[HasInvoice]=0");
            }
            else
            {
                builder.Field("[InvoiceCategory]=@Category", new { invoice.Category })
                    .Field("[InvoiceTitle]=@Title", new { invoice.Title })
                    .Field("[InvoiceContent]=@Content", new { invoice.Content });
            }
            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public Order GetOrderMemo(string id, int userId, bool isSeller)
        {
            var builder = new RegexSqlBuilder().Field("[Id]")
                                          .Table(Database.Order.Table.Order)
                                          .Where("Id=@Id", new { id });
            if (isSeller)
            {
                builder.Field("[SellerFlag],[SellerMemo]");

                builder.Where("[SellerId]=@UserId", new { userId });
            }
            else
            {
                builder.Field("[BuyerFlag],[BuyerMemo],[BuyerMessage]");
                builder.Where("[BuyerId]=@UserId", new { userId });
            }

            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Query<Order>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public bool UpdateOrderBuyerMessage(string id, int userId, string message)
        {
            var builder = new RegexSqlBuilder()
                .Field("[BuyerMessage]=@Message", new { message })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id AND [BuyerId]=@UserId", new { id, userId });

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UpdateOrderMemo(string id, int userId, bool isSeller, byte? flag, string memo)
        {
            var builder = new RegexSqlBuilder()
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id", new { id });
            if (isSeller)
            {
                builder.Field("[SellerMemo]=@Memo", new { memo });
                if (flag.HasValue && flag.Value > 0)
                {
                    builder.Field("[SellerFlag]=@Flag", new { flag });
                }
                builder.Where("[SellerId]=@UserId", new { userId });
            }
            else
            {
                builder.Field("[BuyerMemo]=@Memo", new { memo });
                if (flag.HasValue && flag.Value > 0)
                {
                    builder.Field("[BuyerFlag]=@Flag", new { flag });
                }
                builder.Where("[BuyerId]=@UserId", new { userId });
            }

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UpdateOrderAddress(string id, int userId, bool isSeller, UserAddress address)
        {
            var builder = new RegexSqlBuilder()
                .Field("[Receiver]=@Name", new { address.Name })
                .Field("[Phone]=@Phone", new { address.Phone })
                .Field("[Mobile]=@Mobile", new { address.Mobile })
                .Field("[AreaId]=@AreaId", new { address.AreaId })
                .Field("[Province]=@Province", new { address.Province })
                .Field("[City]=@City", new { address.City })
                .Field("[District]=@District", new { address.District })
                .Field("[Address]=@Address", new { address.Address })
                .Field("[PostalCode]=@PostalCode", new { address.PostalCode })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id", new { id });
            builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public Order OrderStatusGet(string id, int? userId, bool isSeller)
        {
            var builder = new RegexSqlBuilder().Field("[Id],[Category],[Status]")
                                          .Table(Database.Order.Table.Order)
                                          .Where("Id=@Id", new { id });
            builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });

            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Query<Order>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public bool OrderStatusUpdate(string id, int userId, bool isSeller, OrderStatus status, string message = null)
        {
            var builder = new RegexSqlBuilder()
                .Field("[Status]=@Status", new { status })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id", new { id }).
                 Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            if (status == OrderStatus.WaitSellerSend)
            {
                builder.Field("[PayTime]=getdate()");
            }
            if (status == OrderStatus.WaitBuyerConfirm)
            {
                builder.Field("[ConsignTime]=getdate()");
            }
            if (status == OrderStatus.Finished)
            {
                builder.Field("[EndTime]=getdate()");
            }
            if (status == OrderStatus.Canceled)
            {
                var cancel = (byte)(isSeller ? 2 : 1);
                builder.Field("[Cancel]=@Cancel", new { cancel });
                builder.Field("[CancelReason]=@Message", new { message });
            }
            //if(status==OrderStatus.Canceled)
            //{
            //    builder.Field("[CancelReason]=@Message",new{message});
            //}
            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UpdateOrderExpiration(string id, int userId, int delay)
        {
            var builder = new RegexSqlBuilder()
                .Field("[ExpirationTime]=CONVERT(date,dateadd(d,@delay,ExpirationTime),121)", new { delay = delay + 1 })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id", new { id }).
                 Where("[SellerId]=@UserId", new { userId });

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool DeleteOrder(string id)
        {
            throw new NotImplementedException();
        }

        //public string GetAuctionOrderId(long userId, long auctionId)
        //{
        //    var builder = new RegexSqlBuilder().Field("[Id]")
        //                                  .Table(Database.Order.Table.Order)
        //                                  .Where("[OrderStatus]<2 AND [PaymentStatus]=0")
        //                                  .Where("[UserId]=@UserId", new {userId})
        //                                  .Where("AssociatedId=@AssociatedId", new {associatedId = auctionId.ToString()});

        //    var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
        //    using (var connection = GetConnection(ConnectionName.Order))
        //    {
        //        return connection.Query<string>(template.Sql,template.Param).FirstOrDefault();
        //    }
        //}

        public Order GetOrder(string id)
        {
            return GetOrder(id, null);
        }

        public Order GetOrder(string id, int? userId = null, bool inclItem = false, bool isSeller = false, bool inclNote = false)
        {
            Order o;
            SqlTemplate template;
            var builder = new RegexSqlBuilder();

            var sb = new StringBuilder();
            if (userId.HasValue && userId.Value > 0)
            {
                if (isSeller)
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id AND [SellerId]=@UserId;",
                                    Database.Order.Table.Order);
                }
                else
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id AND [BuyerId]=@UserId;",
                                    Database.Order.Table.Order);
                }
            }
            else
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                Database.Order.Table.Order);
            }
            if (inclItem)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderItem);
            }

            if (inclNote)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderNote);
            }
            if (userId.HasValue && userId.Value > 0)
            {
                template = builder.Complete(sb.ToString(), new { id, userId });

            }
            else
            {
                template = builder.Complete(sb.ToString(), new { id });
            }

            using (var connection = GetConnection(ConnectionName.Trade))
            using (var multi = connection.QueryMultiple(template.Sql, template.Param))
            {
                o = multi.Read<Order>().FirstOrDefault();
                if (o != null)
                {
                    if (inclItem)
                    {
                        o.OrderItems = multi.Read<OrderItem>().ToList();
                    }
                    if (inclNote)
                    {
                        o.OrderNotes = multi.Read<OrderNote>().ToList();
                    }
                }
            }
            return o;
        }

        public PagedList<Order> GetPagedOrders(int index = 1, int size = 20, string id = null, int? userId = null,
                                              string nick = null, bool isSeller = false, string title = null,
                                              OrderCategory category = OrderCategory.Normal,
                                              OrderStatus? orderStatus = null, byte? commentStatus = null,
                                              DateTime? beginDate = null, DateTime? endDate = null)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Order.Table.Order)
            .Where("[Category]=@Category", new { category })
            .OrderBy("[CreatedOn] DESC")
            .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(id))
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(nick))
            {
                builder.Where(isSeller ? "[SellerNick]=@Nick" : "[BuyerNick]=@Nick", new { nick });
            }
            if (orderStatus.HasValue && orderStatus.Value >= 0)
            {
                builder.Where("[Status]=@OrderStatus", new { orderStatus });
            }

            var template = builder.Complete(RegexSqlTemplate.PagedSelect);

            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            {
                return connection.PagedList<Order>(template);
            }
        }

        public PagedList<Order> OrderPagedList(int page = 1, int size = 20, string id = null, string name = null, bool isSeller = false, int? userId = null, string userName = null, OrderCategory category = OrderCategory.Normal, OrderStatus? status = null, byte? comment = null, DateTime? beginDate = null, DateTime? endDate = null)
        {

            /*
    var sql=@"declare @table table(row int,id varchar(26),count int);
    with [temp.table] as (select row_number() over(order by userid) as row,Userid from T_users where userid>720 and userid<1200)
    ,[Temp.Count] as (select count(0) [Count] from [temp.table])
    insert into @table select * from [temp.table],[Temp.Count] WHERE row between 10 and 20
    select top 1 [Count] as [Count] from @table
    select b.* from @table as a inner join T_users b on b.UserId =a.Id
    select b.* from @table as a inner join T_orderInfo b on b.UserId =a.Id";
    */
            var sql = @"declare @table table(row int,id varchar(26));
            with [temp.table] as (select row_number() over(/**orderby**/) as row,id from /**table**/ (nolock) /**where**/)
            insert into @table select * from [temp.table] ;
            select count(0) as [count] from @table ;
            select a.* from @table temp inner join [Order] a on temp.id=a.Id where temp.row between @begin and @end ;
            select a.* from @table temp inner join [Order.Item] a on temp.id=a.OrderId where temp.row between @begin and @end ;";

            var builder = new RegexSqlBuilder().Table(Database.Order.Table.Order)
            .Where("[Category]=@Category", new { category })
            .OrderBy("[CreatedOn] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            if (!string.IsNullOrEmpty(id))
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Id] in(select main.Id from [Order] as main INNER JOIN [Order.Item] as sub on sub.OrderId=main.Id WHERE  sub.[Title]=@Name group by main.Id)", new { name });
            }
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(userName))
            {
                builder.Where(isSeller ? "[SellerNick]=@UserName" : "[BuyerNick]=@UserName", new { userName });
            }
            if (status.HasValue && status.Value >= 0)
            {
                builder.Where("[Status]=@OrderStatus", new { status });
            }

            var template = builder.Complete(sql);
            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            {
                return connection.PagedList<Order, OrderItem, string>(template, a => a.Id, b => b.OrderId, (a, b) => { a.OrderItems = b.ToList(); });
            }
        }

        public OrderItem GetOrderItem(string id, int userId, bool isSeller, bool inclOrder = false)
        {
            var builder = new RegexSqlBuilder().Field("*")
                .Table(Database.Order.Table.OrderItem + " a")
                .Where("a.[Id]=@Id", new { id })
                .Where(isSeller ? "a.[SellerId]=@UserId" : "a.[BuyerId]=@UserId", new { userId });
            if (inclOrder)
            {
                builder.InnerJoin(Database.Order.Table.Order + " b on a.[OrderId]=b.[Id]");
            }
            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            {
                if (inclOrder)
                {
                    return connection.Query<OrderItem, Order, OrderItem>(template.Sql, (item, order) => { item.Order = order; return item; }, template.Param)
                        .FirstOrDefault();
                }
                return connection.Query<OrderItem>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public OrderItem GetOrderItemStatus(string id, int userId, bool isSeller)
        {
            var builder = new RegexSqlBuilder().Field("[RefundId],[RefundStatus],[Status],[Version]")
            .Table(Database.Order.Table.OrderItem)
            .Where("a.[Id]=@Id", new { id })
            .Where(isSeller ? "a.[SellerId]=@UserId" : "a.[BuyerId]=@UserId", new { userId });
            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Query<OrderItem>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public bool UpdateOrderItemRefundStatus(string id, int userId, bool isSeller, RefundStatus status,
                                                byte[] version, string refundId)
        {
            var builder = new RegexSqlBuilder()
                .Field("[RefundStatus]=@Status", new { status })
                .Table(Database.Order.Table.OrderItem)
                .Where("Id=@Id", new { id }).
                 Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            if (!string.IsNullOrEmpty(refundId))
            {
                builder.Field("[RefundId]=@RefundId", new { refundId });
            }

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        private static bool UpdateOrderItemRefundStatusInner(IDbConnection connection, IDbTransaction transaction,
                                                             Refund o, bool isSeller)
        {
            var builder = new RegexSqlBuilder()
            .Field("[RefundId]=@RefundId,[RefundStatus]=@Status", new { RefundId = o.Id, o.Status })
            .Table(Database.Order.Table.OrderItem)
            .Where("Id=@Id", new { Id = o.OrderItemId }).
            Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId = isSeller ? o.SellerId : o.BuyerId });

            var template = builder.Complete(RegexSqlTemplate.Update);
            return connection.Execute(template.Sql, template.Param, transaction) == 1;
        }


        public IList<OrderItem> GetOrderItems(string[] orderIds)
        {
            //var ids = orderIds.Aggregate((a, b) => "'" + a + "','" + b + "'").TrimEnd(',');
            var sb = new StringBuilder();
            foreach (var i in orderIds)
            {
                sb.Append("'" + i + "',");
            }
            var ids = sb.ToString().TrimEnd(',');
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Order.Table.OrderItem)
            .Where("[OrderId] in (" + ids + ")");

            var template = builder.Complete(RegexSqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Query<OrderItem>(template.Sql, template.Param).ToList();
            }
        }

        public bool OrderConfirm(string shippingId, int userId)
        {
            var param = new DynamicParameters();

            param.Add("@userId", userId, DbType.Int32);
            param.Add("@shippintId", shippingId, DbType.String);

            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            {
                return connection.Query<bool>("[Proc.Order.Confirm]", param, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
        }

        public bool OrderAutoFinished(UserTrade trade, Order order)
        {
            var now = DateTime.Now;
            if (!order.PaidOn.HasValue)
            {
                order.PaidOn = now;
            }
            var xElement = new XElement("xml");
            var time = now.ToString("yyyy-MM-dd HH:mm:ss:fff");

            xElement.Add(new XElement("order",
                                     new XAttribute("Id", order.Id),
                                     new XAttribute("PayTime", time),
                                     new XAttribute("EndTime", time),
                                     new XAttribute("ModifiedOn", time),
                                     new XAttribute("ConsignTime", time),
                                     new XAttribute("Status", (byte)order.Status)
                            ));
            xElement.Add(new XElement("items", order.OrderItems.Select(x => new XElement("item",
                                                                                         new XAttribute("Id", x.Id),
                                                                                         new XAttribute("EndTime", time),
                                                                                         new XAttribute("ModifiedOn", time),
                                                                                         new XAttribute("ConsignTime", time),
                                                                                         new XAttribute("ShippingType", (byte)x.Shipment),
                                                                                         new XAttribute("Status", (byte)x.Status),
                                                                                         new XAttribute("Extra", x.Extra)
                                                                                ))));
            var xml = xElement.ToString();

            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);
            param.Add("@tradId", trade.Id, DbType.String);
            param.Add("@status", false, DbType.Boolean, ParameterDirection.Output);

            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            {
                connection.Execute("[Proc.Order.AutoFinished]", param, commandType: CommandType.StoredProcedure);
                return param.Get<bool>("@status");
            }
        }

        public OrderExchangeDetail GetOrderExchangeDetail(string orderId, int userId)
        {
            var builder = new RegexSqlBuilder().Field("*")
                .Table(Database.Order.Table.OrderExchangeDetail)
                .Where("OrderId=@OrderId", new { orderId })
                .Where("[BuyerId]=@UserId", new { userId });

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            {
                return connection.Query<OrderExchangeDetail>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        #endregion

        #region Refund

        public bool CreateRefund(Refund o)
        {
            var result = false;
            var builder = new RegexSqlBuilder()
                .Table(Database.Order.Table.Refund)
                .Field(
                    "([Id],[Category],[OrderId],[OrderItemId],[Total],[Return],[Payment],[BuyerId],[BuyerNick],[SellerId],[SellerNick],[SkuId],[ItemId],[ItemTitle],[ItemPicture],[Quantity],[Price],[ReasonId],[Reason],[Desc],[Attachments],[GoodsReturn],[GoodsReturnTime],[GoodsStatus],[ShippingId],[RefundAddress],[ShippingCompany],[ExpressNo],[Timeout],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@Id,@Category,@OrderId,@OrderItemId,@Total,@Return,@Payment,@BuyerId,@BuyerNick,@SellerId,@SellerNick,@SkuId,@ItemId,@ItemTitle,@ItemPicture,@Quantity,@Price,@ReasonId,@Reason,@Desc,@Attachments,@GoodsReturn,@GoodsReturnTime,@GoodsStatus,@ShippingId,@RefundAddress,@ShippingCompany,@ExpressNo,@Timeout,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {
                        o.Id,
                        o.Category,
                        o.OrderId,
                        o.OrderItemId,
                        o.Total,
                        o.Return,
                        o.Payment,
                        o.BuyerId,
                        o.BuyerNick,
                        o.SellerId,
                        o.SellerNick,
                        o.SkuId,
                        o.ItemId,
                        o.ItemTitle,
                        o.ItemPicture,
                        o.Quantity,
                        o.Price,
                        o.ReasonId,
                        o.Reason,
                        o.Desc,
                        o.Attachments,
                        o.GoodsReturn,
                        o.GoodsReturnTime,
                        o.GoodsStatus,
                        o.ShippingId,
                        o.RefundAddress,
                        o.ShippingCompany,
                        o.ExpressNo,
                        o.Timeout,
                        o.Extra,
                        o.Status,
                        o.ModifiedOn,
                        o.CreatedBy,
                        o.CreatedOn
                    });
            var template = builder.Complete(RegexSqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var status = connection.Execute(template.Sql, template.Param, transaction) == 1;
                    if (status)
                    {
                        status = UpdateOrderItemRefundStatusInner(connection, transaction, o, false);
                        if (status)
                        {
                            transaction.Commit();
                            result = true;
                        }
                        else
                        {
                            transaction.Rollback();
                        }
                    }
                }
                catch (Exception)
                {
                    transaction.Rollback();
                }
            }
            return result;
        }

        public Refund GetRefund(string id, int userId, bool isSeller = false)
        {
            var builder = new RegexSqlBuilder().Field("*")
                                          .Table(Database.Order.Table.Refund)
                                          .Where("[Id]=@Id", new { id })
                                          .Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Trade))
            {

                return connection.Query<Refund>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        #endregion

        #region Shipment

        public MagicKit OrderShipmentCreate(string orderId, string orderItemId, OrderStatus orderStatus, int senderId, bool multiple, Shipment o)
        {
            var result = new MagicKit();
            var extra = o.ShipmentExtra;
            var a = new SqlBuilder()
                .Insert(Database.Trade.Table.Shipment)
                .Values(o.Id, o.Category, o.OrderId, o.OrderItemId, o.ExpressNo, o.Title, o.Summary, o.IsQuickCod,
                        o.DeliveryStart, o.DeliveryEnd, o.Payer, o.Receiver, o.Company, o.Phone, o.Mobile, o.AreaId,
                        o.Province, o.City, o.District, o.Address, o.PostalCode, o.CompanyId, o.CompanyName, o.SenderId,
                        o.ReceiverId, o.ReceiverName, o.IsOnline, o.Extra, o.Status, o.CreatedBy, o.CreatedOn);
            var b = new SqlBuilder()
                .Insert(Database.Trade.Table.ShipmentExtra)
                .Values(extra.Id, extra.Sender, extra.Company, extra.Phone, extra.Mobile, extra.AreaId, extra.Province,
                        extra.City, extra.District, extra.Address, extra.PostalCode, extra.Receiver,
                        extra.ReceiverCompany, extra.ReceiverPhone, extra.ReceiverMobile, extra.ReceiverAreaId,
                        extra.ReceiverProvince, extra.ReceiverCity, extra.ReceiverDistrict, extra.ReceiverAddress,
                        extra.ReceiverPostalCode);
            const string sql = @"declare @sql nvarchar(1000)='update [Order.Item] set [Status]=4,[ConsignOn]=GETDATE(),[ModifiedOn]=GETDATE() where [Id] in(select '''+replace({0},',',''' union all select ''')+''')' 
            exec(@sql)  update [Order] set [MultipleConsign] ={1}, [ConsignOn]=GETDATE(),[Status] ={2},[ModifiedOn]=GETDATE() where [Id] ={3} and [SellerId] = {4}";
            var c = new SqlBuilder(sql, orderItemId, multiple, orderStatus, orderId, senderId);
            var template = new SqlBuilder().Append(a).Append(b).Append(c).Complete();

            using (var connection = GetConnection(ConnectionName.Trade).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    connection.Execute(template.Sql, template.Param, transaction);
                    transaction.Commit();
                    result.Status = true;
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result.Message = e.Message;
                }
            }
            return result;
        }

        public Shipment ShipmentGet(string id)
        {
            var builder = new RegexSqlBuilder().Field("*")
                .Table(Database.Trade.Table.Shipment)
                .Where("[Id]=@Id", new {id});

            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);

            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Query<Shipment>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<Shipment> OrderShipmentGet(string orderId)
        {
            var builder = new RegexSqlBuilder().Field("*")
                .Table(Database.Trade.Table.Shipment)
                .Where("[OrderId]=@OrderId", new {orderId});

            var template = builder.Complete(RegexSqlTemplate.Select);

            using (var connection = GetConnection(ConnectionName.Trade))
            {
                return connection.Query<Shipment>(template.Sql, template.Param).ToList();
            }
        }

        public string GetSplitShippingOrderId(string orderId)
        {
            var result = string.Empty;
            List<string> array;
            var builder = new RegexSqlBuilder().Field("[OrderItemId]")
                                          .Table(Database.Trade.Table.Shipment)
                                          .Where("[OrderId]=@OrderId", new { orderId });

            var template = builder.Complete(RegexSqlTemplate.Select);

            using (var connection = GetConnection(ConnectionName.Trade))
            {
                array = connection.Query<string>(template.Sql, template.Param).ToList();
            }
            if (array.Count > 0)
            {
                result = array.Aggregate(result, (current, o) => current + (o + ","));
                return result.TrimEnd(',');
            }
            return result;
        }

        #endregion

        #region IService
        public void CachePurge()
        {
            PurgeCache();
        }

        public PagedList<Magic<string, string, int>> CacheGet(int page = 1, int size = 50, int hitCount = 0)
        {
            return GetPagedCache(page, size, hitCount);
        }
        #endregion
    }
}
